﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class UserEntity
    {
        public Int32 Id { get; set; }
        public string UName { get; set; }
        public string UPassword { get; set; }
        public string UType { get; set; }
        public Int32 Group { get; set; }
    }

    public class User
    {
        private static string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb' ;Jet OLEDB:Database Password=Yarabe11";
        private static string CmdText = "";

        public static String ADMIN_USER_UType = "مشرف عام";
        public static String SUPERVISOT_USER_UType = "مشرف";
        public static String NORMAL_USER_UType = "مدخل بيانات";


        private static UserEntity FillEntityFromReader(OleDbDataReader reader)
        {
            UserEntity x = new UserEntity();
            x.Id = Int32.Parse(reader["Id"].ToString());
            if (reader["UName"] != null)
                x.UName = reader["UName"].ToString();
            if (reader["UPassword"] != null)
                x.UPassword = reader["UPassword"].ToString();
            if (reader["UType"] != null)
                x.UType = reader["UType"].ToString();
            if (reader["Group_Id"] != null)
                x.Group = Convert.ToInt32(reader["Group_Id"]);



            return x;
        }


        public static List<UserEntity> GetAllPropUsers()
        {
            UserEntity x;
            List<UserEntity> PropUsersList = new List<UserEntity>();
            CmdText = "Select * from PropUsers ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    PropUsersList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return PropUsersList;

        }


        public static UserEntity GetUserByName(string Name)
        {
            UserEntity x = null ;
            CmdText = "Select * from PropUsers where UName = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@UName", Name);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    x = FillEntityFromReader(reader);
               
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }


        public static UserEntity GetUserById(Int32 Id)
        {
            UserEntity x = null;
         
            CmdText = "Select * from PropUsers where Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }



        public static bool Add(UserEntity P)
        {
            bool propUserId = true;

            CmdText = "INSERT INTO PropUsers (UName,UPassword,UType,Group_Id) values (?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@UName", P.UName);
            cmd.Parameters.AddWithValue("@UPassword", P.UPassword);
            cmd.Parameters.AddWithValue("@UType", P.UType);
            cmd.Parameters.AddWithValue("@Group_Id", P.Group);

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    propUserId = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى ادخال البيانات" + ex.Message);
                propUserId = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return propUserId;
        }

        public static bool Update(UserEntity P)
        {
            bool flag = true;

            CmdText = "Update PropUsers set UName = ? , UPassword = ? , UType = ? , Group_Id = ? where Id=? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@UName", P.UName);
            cmd.Parameters.AddWithValue("@UPassword", P.UPassword);
            cmd.Parameters.AddWithValue("@UType", P.UType);
            cmd.Parameters.AddWithValue("@Group", P.Group);
            cmd.Parameters.AddWithValue("@Id", P.Id);

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static bool Delete(int Id)
        {
             bool flag = true;
            CmdText = "delete from PropUsers where Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);


            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى حذف البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

    }
}
